package edu.iiitb.studentinfo.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import edu.iiitb.studentinfo.model.Announcement;
import edu.iiitb.studentinfo.model.Friend;
import edu.iiitb.studentinfo.model.Mark;
import edu.iiitb.studentinfo.model.Student;
import edu.iiitb.studentinfo.util.DB;
import edu.iiitb.studentinfo.util.DBconn;

public class StudentService {
	private DBconn dbconn = null;
	private Connection con = null;
	private PreparedStatement pst = null;
	private Statement stmt = null;
	private ResultSet resultSet = null;
	private String query = null;
	
	public Student getStudentInfo(int studentId) {
		Student student = new Student();
		String query = "select * from student_information where studentid=?";
		int specializationId;
		/*Connection connection = DB.getConnection();
		ResultSet resultSet = DB.readFromDB(query, connection);
		try {
			student.setFirstName(resultSet.getString(1));
			//student.setLastName(resultSet.getString("lastName"));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}*/
		try {
			dbconn = new DBconn();
			con = dbconn.getConnection();
			pst = con.prepareStatement(query);
			pst.setInt(1, studentId);
			
			resultSet = pst.executeQuery();
			resultSet.next();
//			System.out.println("first name="+resultSet.getString("firstName"));
//			System.out.println("last name="+resultSet.getString("lastName"));
			student.setFirstName(resultSet.getString("firstName"));
			student.setLastName(resultSet.getString("lastName"));
			student.setRollNo(resultSet.getString("RollNo"));
			student.setEmail(resultSet.getString("email"));
			student.setPhone(resultSet.getString("phone"));
			specializationId = resultSet.getInt("specializationid");
			student.setSpecializationId(specializationId);
			String query2 = "select * from student_specialization where specializationid=?";
			PreparedStatement pst2 = con.prepareStatement(query2);
			pst2.setInt(1, specializationId);
			ResultSet resultSet2 = pst2.executeQuery();
			resultSet2.next();
			student.setSpecialization(resultSet2.getString("specialization"));
			student.setFriends(getFriends(studentId));
			student.setMarks(getMarks(studentId));
			

		} catch (SQLException e) {
			System.out.println(e.getMessage());
			studentId = -1;
		} finally {
			dbconn.close(resultSet);
			dbconn.close(pst);
			dbconn.close(con);
		}
		
		return student;
	}
	
	public ArrayList<Friend> getFriends(int studentId) {
		ArrayList<Friend> friends = new ArrayList<Friend>();
		Friend friend = null;
		query = "SELECT studentid,firstName,lastName,RollNo FROM student_information where studentid in (SELECT friendid FROM student_friendlist WHERE studentid=?) ";

		try {

			dbconn = new DBconn();
			con = dbconn.getConnection();
			pst = con.prepareStatement(query);
			pst.setInt(1, studentId);
			resultSet = pst.executeQuery();

			while (resultSet.next()) {
				friend = new Friend();
				friend.setStudentid(resultSet.getInt("studentid"));
				friend.setFirstName(resultSet.getString("firstName"));
				friend.setLastName(resultSet.getString("lastName"));
				friend.setRollNo(resultSet.getString("RollNo"));
				
				friends.add(friend);
			}

		} catch (Exception e) {
			System.out.println("error occured");
			System.out.println(e.getMessage());
		} finally {
			dbconn.close(resultSet);
			dbconn.close(pst);
			dbconn.close(con);
		}

		return friends;
	}
	
	
	public ArrayList<Mark> getMarks(int studentId) {
		ArrayList<Mark> marks = new ArrayList<Mark>();
		Mark mk = null;
		query = "SELECT sm.subjectid,subjectName,marksobtained,maxmarks FROM student_marks sm, student_subject ss where sm.studentid=? and sm.subjectid=ss.subjectid";

		try {

			dbconn = new DBconn();
			con = dbconn.getConnection();
			pst = con.prepareStatement(query);
			pst.setInt(1, studentId);
			resultSet = pst.executeQuery();

			while (resultSet.next()) {
				mk = new Mark();
				mk.setSubjectid(resultSet.getInt("subjectid"));
				mk.setSubjectName(resultSet.getString("subjectName"));
				mk.setMarksObtained(resultSet.getInt("marksobtained"));
				mk.setMaxMarks(resultSet.getInt("maxmarks"));
				
				marks.add(mk);
			}

		} catch (Exception e) {
			System.out.println("error occured");
			System.out.println(e.getMessage());
		} finally {
			dbconn.close(resultSet);
			dbconn.close(pst);
			dbconn.close(con);
		}

		return marks;
	}
	
	public ArrayList<Announcement> getAnnouncements(int specializationid) {
		ArrayList<Announcement> announcements = new ArrayList<Announcement>();
		Announcement an = null;
		query = "SELECT announcementid, announcement FROM student_announcements where specializationid=?";
		
		try {
			
			dbconn = new DBconn();
			con = dbconn.getConnection();
			pst = con.prepareStatement(query);
			pst.setInt(1, specializationid);
			resultSet = pst.executeQuery();
			
			while(resultSet.next()) {
				an = new Announcement();
				an.setAnnouncementid((int) resultSet.getInt("announcementid"));
				an.setAnnouncement((String) resultSet.getString("announcement"));
				announcements.add(an);
				System.out.println(an.getAnnouncement());
			}
			
		} catch (Exception e) {
			System.out.println("error occured");
			System.out.println(e.getMessage());
		} finally {
			dbconn.close(resultSet);
			dbconn.close(pst);
			dbconn.close(con);
		}
		
		return announcements;
	}
	
	
}
